package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.StudyPlan;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

@Mapper
public interface StudyPlanMapper {

    // 简单的测试查询
    @Select("SELECT COUNT(*) FROM study_plans WHERE user_id = #{userId}")
    int countByUserId(@Param("userId") Long userId);

    @Select("SELECT * FROM study_plans WHERE user_id = #{userId} AND DATE(task_date) >= CURDATE() ORDER BY task_date ASC, status ASC")
    @Results({
            @Result(property = "taskId", column = "task_id"),
            @Result(property = "planId", column = "plan_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "taskDate", column = "task_date"),
            @Result(property = "status", column = "status"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at")
    })
    List<StudyPlan> findTodayPlans(@Param("userId") Long userId);

    @Select("SELECT plan_id FROM study_plans WHERE user_id = #{userId} LIMIT 1")
    Long getAvailablePlanId(@Param("userId") Long userId);

    @Insert("INSERT INTO study_plans (plan_id, plan_name, user_id, start_date, end_date, status, plan_content, target_subject_id, created_at, updated_at) " +
            "VALUES (#{planId}, #{planName}, #{userId}, #{startDate}, #{endDate}, #{status}, #{content}, #{targetSubjectId}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "taskId")
    void insert(StudyPlan plan);

    @Select("SELECT * FROM study_plans WHERE task_id = #{taskId}")
    @Results({
            @Result(property = "taskId", column = "task_id"),
            @Result(property = "planId", column = "plan_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "taskDate", column = "task_date"),
            @Result(property = "status", column = "status"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at")
    })
    StudyPlan findById(@Param("taskId") Long taskId);

    @Update("UPDATE study_plans SET status = #{status}, updated_at = CURRENT_TIMESTAMP " +
            "WHERE task_id = #{taskId}")
    void update(StudyPlan plan);

    @Delete("DELETE FROM study_plans WHERE task_id = #{taskId}")
    void delete(@Param("taskId") Long taskId);

    @Select("SELECT * FROM study_plans WHERE user_id = #{userId} AND status = '未完成' AND task_date < CURDATE() ORDER BY task_date DESC")
    @Results({
            @Result(property = "taskId", column = "task_id"),
            @Result(property = "planId", column = "plan_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "taskDate", column = "task_date"),
            @Result(property = "status", column = "status"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at")
    })
    List<StudyPlan> findHistoryUnfinishedPlans(@Param("userId") Long userId);

    @Select("SELECT DATE_FORMAT(task_date, '%Y-%m-%d') as completed_date " +
            "FROM study_plans " +
            "WHERE user_id = #{userId} " +
            "AND DATE_FORMAT(task_date, '%Y-%m') = #{month} " +
            "GROUP BY DATE_FORMAT(task_date, '%Y-%m-%d') " +
            "HAVING COUNT(*) > 0 AND COUNT(CASE WHEN status = '已完成' THEN 1 END) = COUNT(*) " +
            "AND COUNT(CASE WHEN status = '未完成' THEN 1 END) = 0")
    List<String> findCompletedDays(@Param("userId") Long userId, @Param("month") String month);

    @Select("SELECT * FROM study_plans WHERE user_id = #{userId} " +
            "AND start_date <= #{date} AND end_date >= #{date} LIMIT 1")
    StudyPlan findPlanByDateRange(@Param("userId") Long userId,
                                  @Param("date") Date date, Date taskDate);

    @Select("SELECT sp.* FROM study_plans sp JOIN users u ON sp.user_id = u.user_id WHERE u.username = ? ORDER BY sp.task_date DESC")
    @Results({
            @Result(property = "taskId", column = "task_id"),
            @Result(property = "planId", column = "plan_id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "taskDate", column = "task_date"),
            @Result(property = "status", column = "status"),
            @Result(property = "content", column = "content"),
            @Result(property = "createdAt", column = "created_at"),
            @Result(property = "updatedAt", column = "updated_at")
    })
    List<StudyPlan> findPlansByUsername(@Param("username") String username);

    @Select("SELECT MAX(plan_id) FROM study_plans")
    Integer selectMaxPlanId();
}